<?php


class Hotel_model extends MY_Model {
	
	function Hotel_model() {
		parent::__construct();
	}
	
	
	public function getLayoutInfo() {
		
		// path location of the web site
		$data['base_url']				= $this->config->item('base_url');
		
		// create the links to translate the page
		$switch_lang				= 	anchor($this->lang->switch_uri('en'),'en')." | ";
		$switch_lang				.= 	anchor($this->lang->switch_uri('pt'),'pt');
		$data['switch_lang']		= 	$switch_lang;
		
		// load the content text data ( all data is loaded ... it doesnt look the perfect solution ... find another one :0) )
		$data['text_main']			= $this->lang->line('hotel.text.main');
		$data['text_lnk1']			= $this->lang->line('hotel.text.lnk1');
		$data['text_lnk2']			= $this->lang->line('hotel.text.lnk2');
		$data['text_lnk3']			= $this->lang->line('hotel.text.lnk3');
		
		// ... more data
		$data['website_title']		= 'Radical Booking';
		$data['title']		 		= 'Hotel';
		$data['login']				= anchor("Hotel/logout","logout")." ".$this->ion_auth->get_user()->email;
		$data['subtitle'] 			= $this->lang->line('hotel.text.subtitle');
		$data['footer'] 			= 'ï¿½ copyright Softingal';
		
		return $data;
	}
	
	
	
	
	/**
	 * function to retrieve an array with 'school_id'=>'company' of all schools 
	 * associated with the given hotel 'id'
	 * @param int $id hotel id
	 * @return array with 'school_id'=>'company'
	 */
	public function getHotelASschool($id) {
		
		$schoolNames = array();
		
		$this->db->where('hotel_id',$id);
		
		$query = $this->db->get('sh');
		
 		$result = $query->result();
		
		foreach ($result as $key => $value) {
			$schoolNames[$value->school_id] = $this->ion_auth->get_user($value->school_id)->company;
		}
		
		return $schoolNames;
	}
	
	
	/**
	 * Get the id of the school associated to the given hotel id
	 * @param int $id hotel identification
	 * @return int $id school identification or false if there is no $id to return
	 */
	public function getSchoolID($id) {
		
		$this->db->where('hotel_id',$id);
		
		$query = $this->db->get('sh');
		
		$row = $query->row();
		
		if ($query->num_rows()>0)
			return $row->school_id;
		else
			return false;
	}
	
	
	
	/**
	 * retrieve the schoolId associated to the given class
	 * @param unknown_type $classId
	 */
	public function getSchoolID_from_classID($classId) {
		
		$this->db->select('users_id')->from('class')->where('class.id', $classId);
		
		$this->db->join('offer','offer.id = class.offer_id','inner');
		
		$schoolId = $this->db->get()->row()->users_id;
		
		return $schoolId;	
	}
	
	
	
	
	
	
	public function createClient($data) {
		
		$users_id = $this->ion_auth->get_user()->id;
		
		$this->db->set('users_id',$users_id);
		
		$succ = $this->db->insert('client',$data);
		
		if ($succ)
			return $this->db->insert_id();
		else 
			return false;
	}
	
	
	public function updateClient() {
		
	}
	
	public function deleteClient() {
		
	}
	
	
	
	
	
	
	public function createReservation($data) {
		
		
	}
	
	public function readReservation() {
		
		// only show reservation's for the current log in hotel
		$users_id = $this->ion_auth->get_user()->id;

		//$this->db->select('*');
 		$this->db->select('
 			users.id			as userId,
 			users.group_id		as groupId,
 			client.id			as clientId,
 			client.name			as clientName,
 			reservation.id		as reservationId,
 			reservation.date	as reservationDate,
 			offer.id			as offerId,
 			offer.name			as offerName
 		');

		$this->db->from('reservation');
		$this->db->join('client','client.id = reservation.client_id');
		$this->db->join('users','users.id = client.users_id');
		$this->db->join('offer','offer.id = reservation.offer_id');
		$this->db->where('users.id',$users_id);
		$query = $this->db->get();
		$rows = $query->result();
		$dataTableResult['data'] = $rows;
		
		//return $rows;
		return json_encode($dataTableResult);
	}
	
	public function updateReservation() {
		
	}
	
	public function deleteReservation() {
		
	}
	

	/**
	 * get the schedules associated to the given userId and classId 
	 * @param int $classId
	 * @param int $userId
	 */
	public function getSchedule($classId,$userId) {
		
		// get the id of the school associated to the hotel with the given userId
 		//$schoolId = $this->db->select('*')->from('sh')->where('hotel_id',$userId)->get()->row()->school_id;
 		
 		$this->db->flush_cache();
		
		$now = date('Y-m-d H:i:s',time());
		
		// users.id is a school for sure because offer is only created by school
		$this->db->select('
			schedule.id			as scheduleId,
			users.id 			as schoolId,
			schedule.start		as start,
			schedule.end		as end,
			offer.id			as offerId
		');
		$this->db->from('schedule');
		$this->db->where('start >',$now);
		$this->db->join('class','class.id = schedule.class_id');
		$this->db->where('class.id',$classId);
		$this->db->join('offer','offer.id = schedule.offer_id');
		$this->db->join('users','users.id = offer.users_id');
		
		// return only the schedules from the school associated to the given hotel (userId)
		//$this->db->where('users.id',$schoolId);
		
		$query = $this->db->get();
		$row = $query->result();
		
		return $row;	
	}
	
	
	
	public function getOffer($offerId,$userId=null,$classId=null) {
		
		if($classId) {
			$offer_id = $this->db->select('offer_id')->from('class')->where('id',$classId)->get()->row()->offer_id;
			return $offer_id;
		}
		
		// get the id of the school associated to the hotel with the given userId
		if($userId)
			$schoolId = $this->db->select('*')->from('sh')->where('hotel_id',$userId)->get()->row()->school_id;
			
		$this->db->flush_cache();
		
		//$now = date('Y-m-d H:i:s',time());
		
		// users.id is a school for sure because offer is only created by school
		$this->db->select('
			class.id			as classId,
			users.id 			as schoolId,
			offer.id			as offerId,
			offer.name			as offerName,
			offer.price			as offerPrice,
			offer.description	as offerDescription,
			offer.level			as offerLevel,
			offer.minPart		as offerMinPart,
			offer.maxPart		as offerMaxPart,
			offer.part			as offerPart
		');
		
		$this->db->from('offer');
		//$this->db->where('start >',$now);
		$this->db->join('users','users.id = offer.users_id');
		$this->db->join('class','class.offer_id = offer.id');
		//$this->db->join('sh','sh.school_id = users.id');
		
		
		$this->db->where('offer.id',$offerId);
		
		if ($userId)
			$this->db->where('users.id',$schoolId);
		
		$query = $this->db->get();
		return $query->result();
	}
	
	
	/**
	 * Get all available classes from all schools associated to this hotel
	 * @return array with all classes or an empty array if no classes are available
	 */
	public function getAllAvailableClasses($schoolID = null) {
		
		$user_id = $this->ion_auth->get_user()->id;
		
		$now = date('Y-m-d H:i:s',time());
		
		// check if this hotel 'has' schools ...
		$result = $this->db->select('school_id')->from('sh')->where('hotel_id', $user_id)->get()->result();
		if (!$result)
			return array();
		
		$queryStrWithSchoolID = '
			select 
				o.users_id 				as schoolId,
			    m.company				as schoolName,
			    o.id					as offer_id,
			    c.id					as class_id,
				o.name					as name,
				o.price					as price,
				o.level					as level,
				o.maxPart				as maxPart,
				o.numberOfClasses		as numberOfClasses,
			    (c.maxPart - c.part)	as spots
			from offer o 
			inner join class 	c on c.offer_id = o.id
			inner join users 	u on u.id 		= o.users_id
			inner join meta 	m on m.user_id 	= u.id 
			where users_id in (select school_id from sh where hotel_id = '.$user_id.' and school_id = '.$schoolID.' group by school_id) and 
			c.id in (select classID from (select class_id, count(*) as qt from schedule s where start >= now() group by class_id) as table1 inner join (select class_id as classID, count(*) as qt from (select * from schedule where class_id in (select class_id from schedule s where start >= now() group by class_id)) as allSch group by class_id) as table2 on table1.class_id = table2.classID and table1.qt = table2.qt group by classID);
		';
		
		$queryStrWithoutSchoolID = '
			select 
				o.users_id 				as schoolId,
			    m.company				as schoolName,
			    o.id					as offer_id,
			    c.id					as class_id,
				o.name					as name,
				o.price					as price,
				o.level					as level,
				o.maxPart				as maxPart,
				o.numberOfClasses		as numberOfClasses,
			    (c.maxPart - c.part)	as spots
			from offer o 
			inner join class 	c on c.offer_id = o.id
			inner join users 	u on u.id 		= o.users_id
			inner join meta 	m on m.user_id 	= u.id 
			where users_id in (select school_id from sh where hotel_id = '.$user_id.' group by school_id) and 
			c.id in (select classID from (select class_id, count(*) as qt from schedule s where start >= now() group by class_id) as table1 inner join (select class_id as classID, count(*) as qt from (select * from schedule where class_id in (select class_id from schedule s where start >= now() group by class_id)) as allSch group by class_id) as table2 on table1.class_id = table2.classID and table1.qt = table2.qt group by classID);
		';
		
		if ($schoolID)
			$query = $this->db->query($queryStrWithSchoolID);
		else 
			$query = $this->db->query($queryStrWithoutSchoolID);
		
		$result = $query->result();
		
		return $result;
	}
		
	
	/**
	 * retrieve the extras associated to a given 'school' 'offer'
	 * @param array $extrasIDs - if you know witch extras you want
	 * @param int $offerId
	 * @param int $schoolId
	 * @param boolean $allExtras - true for all offers or false to only offers not associated to the offer
	 */
	public function getExtras($extrasIDs = null, $offerId=null, $schoolId=null, $allExtras=false) {
		
		// ... if we have the IDs
		if ($extrasIDs)
			return $this->db->select('*')->from('extra')->where_in('id',$extrasIDs)->get()->result();
		
		// fields to select
		$select = 'id, name, price';
		
		// get extras already included in the given offer
		$this->db->select('extra_id')->from('extra_offer');
		$result = $this->db->where('offer_id',$offerId)->get()->result();
		$extrasIncluded = array();
		foreach ($result as $item) {
			array_push($extrasIncluded, $item->extra_id);
		}
		
		// all extras should be returned
		if ($allExtras)
			$extrasIncluded = null;
		
		//$this->firephp->log($extrasIncluded,'extrasIncluded');
		
		// if this offer has no extras included, show them all
		if (empty($extrasIncluded)) {
			$this->db->select($select)->from('extra');
			$extras = $this->db->where('users_id',$schoolId)->get()->result();
			//$this->firephp->log($extras, 'extras');
		}
		else { // show only the extras that are NOT included in the offer
			$this->db->select($select)->from('extra')->where_not_in('id',$extrasIncluded);
			$extras = $this->db->where('users_id',$schoolId)->get()->result();
			//$this->firephp->log($extras, 'extras');
		}
		
		return $extras;
	}
	
	
	
	
	/**
	 * get all registered clients in the given active offer 
	 * @param int $offerId - offer identification
	 * @return array with all clients or an empty array if no available clients associated to the given active offer 
	 */
	public function getClientsInOffer($offerId) {
		
		$users_id = $this->ion_auth->get_user()->id;
	
		$clientIdList = array();
		
		$this->db->select('reserve.client_id');
		$this->db->from('reserve');
		$this->db->join('client','client.id = reserve.client_id','inner');
		$this->db->join('users','users.id = client.users_id','inner');
		$this->db->where('offer_tmp_id', $offerId);
		$this->db->where('users.id', $users_id);
		$result = $this->db->get()->result();
		
		if ($result == null)
			return $clientIdList;
		
		foreach ($result as $item) {
			array_push($clientIdList, $item->client_id);
		}
		
		return $clientIdList;
	}
	
	
	/**
	 * Get an array with all schools IDs associated to current hotel
	 */
	public function getSchools() {
		
		$hotel_id = $this->ion_auth->get_user()->id;
		
		// array to hold the school IDs
		$schoolIDs = array();
		
		// select hotels associated to the given
		$this->db->select('school_id')->from('sh')->where('hotel_id',$hotel_id);	
		$result = $this->db->get()->result();
		
		// append IDs to array
		foreach ($result as $value) {
			array_push($schoolIDs,$value->school_id);
		}
		
		return $schoolIDs;
	}
	
	
	/**
	 * Function that retrieves all class IDs with schedules within the given date range
	 * @param date $start
	 * @param date $end
	 * @return array with class IDs or an empty array
	 */
	public function getClassesWithSchedulesInRange($start, $end) {
		
		$hotel_id = $this->ion_auth->get_user()->id;
		
		$classIDs = array();
		
		$this->db->select('class_id')->from('schedule');
		$this->db->join('offer','offer.id = schedule.offer_id','inner');
		$this->db->join('users','users.id = offer.users_id','inner');
		
		//$this->db->where('start >', $start);
		//$this->db->where('end <', $end);
		//$this->db->where('users.id', $hotel_id);
		
		$this->db->group_by('class_id');
		
		$result = $this->db->get()->result();
		
		foreach ($result as $value) {
			array_push($classIDs, $value->class_id);
		}
		
		$this->db->flush_cache();
		
		return $classIDs;
	}
	
	
	
	/**
	 * Retrieve information about the reserve
	 * @param $id
	 */
	public function getReserveInfo($id) {
		
		$this->db->select('*')->from('reserve');
		$this->db->where('id', $id);
		$result = $this->db->get()->result();
		return $result;
	}
	
	
	
	/**
	 * Check if Offer is available to make reservations
	 * @param int $offerId Offer identification
	 * @return true if it is available or false if it isnt or offer doesnt exist
	 */
	public function isOfferAvailableForReservation($offerId) {
		
		// id of the current hotel user
		$users_id = $this->ion_auth->get_user()->id;
		
		$this->db->select('remainingClasses')->from('offer');
		$this->db->join('users','users.id = offer.users_id','inner');
		$this->db->join('sh','sh.school_id = offer.users_id','inner');
		$this->db->where('sh.hotel_id',$users_id);
		$this->db->where('offer.id',$offerId);
		$result = $this->db->get()->row();

		// if offer is not available or it isnt associated to the hotel
		if (!$result)
			return false;
		
		// '0' means offer is not available for reservation
		if ($result->remainingClasses)
			return false;
		else
			return true;
	}	
	
	/**
	 * check if there is only one free spot left
	 * @param int $offerId
	 */
	public function isLastReservation($offerId,$classId) {
		
		// class
 		$class = $this->db->select('maxPart, part')->from('class')->where('id',$classId)->get()->row();
 		if ($class->part == ($class->maxPart - 1))
 			return true;
 		else 
 			return false;
 		
		// offer
 		$offer = $this->db->select('remainingClasses, numberOfClasses, maxPart, part')->from('offer')->where('id',$offerId)->get()->row();
 		if ($offer->part == ($offer->maxPart - 1))
 			return true;
 		else 
 			return false;
	}
	
	/**
	 * check if the given offer has available spots
	 * @param int $offerId
	 */
	public function isOfferFull($offerId) {
 		$offer = $this->db->select('maxPart, part')->from('offer')->where('id',$offerId)->get()->row();
 		if ($offer->part == $offer->maxPart)
 			return true;
 		else 
 			return false;
	}
	
	
	
	/**
	 * set the languages spoken by a given client
	 * @param int $clientId - client identification
	 * @param array $lang - array with languages spoken by the client
	 */
	public function updateClientLanguage($clientId, $lang) {
		
			$langArr = explode('&', $lang);
			
			$this->db->delete('client_language',array('client_id'=>$clientId));
			
			foreach ($langArr as $domain) {
				$langID = $this->db->select('id')->from('language')->where('domain',$domain)->get()->row()->id;
				$data = array(
					'client_id'		=>$clientId,
					'language_id' 	=>$langID
				);
				$upt = $this->db->insert('client_language',$data);
			}
	}
	
	
	
	
	
	public function addExtra_Reserves($clientReservesId, $extrasIDs=array()) {
		
		if (empty($extrasIDs))
			return false;
		
		if (!$clientReservesId)
			return false;
		
		$inserted = false;
		
		foreach ($extrasIDs as $value) {
			$data = array(
				'extra_id'				=> $value,
				'client_reserves_id'	=> $clientReservesId
			);
			$inserted = $this->db->insert('extra_reserve',$data);
			if($inserted==false) break;
		}
		
		return $inserted;
	}
	
	
	
	
	
	
	/**
	 * @param array $defaults with fields and data that should be set to default if not present
	 * @param array $options where the default values must be placed
	 * @return array with the merged values
	 */
	private function _default($defaults, $options) {
		return array_merge($defaults, $options);
	}
	
	
	/**
	 * Checks if the array has all requiered fields
	 * @param array $required with the requiered fields
	 * @param array $data with the fields to be checked
	 * @return TRUE if the field exists FALSE otherwise
	 */
	private function _required($required, $data) {
		foreach ($required as $field)
			if (!isset($data[$field]))
			return false;
		return true;
	}
	
	
	
	
	
	
	public function tmp() {

		
		
	}
	
	
	
}